package com.project.controller;


import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.project.common.Result;
import com.project.common.UserHolder;
import com.project.entity.UserDb;
import com.project.entity.redis.UserInfo;
import com.project.entity.table.TableDTO;
import com.project.utils.DBUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author Ao
 * @since 2024-04-26
 */
@RestController
@RequestMapping("/userDb")
@Api(tags = "用户数据库相关接口")
public class UserDbController {
    @Resource
    private com.project.service.UserDbService userDbService;


    //查找所有数据接口
    @GetMapping("/findAll")
    @ApiOperation("查询所有用户数据库")
    public Result<List<UserDb>> test() {
        return Result.success(userDbService.list());
    }


    @GetMapping("/selectAllDB2")
    @ApiOperation("查找用户所有连接")
    public Result<List<UserDb>> selectAllDB() {
        UserInfo userInfo = UserHolder.getUser();
        LambdaQueryWrapper<UserDb> l1 = new LambdaQueryWrapper<>();
        l1.eq(UserDb::getUserName, userInfo.getUserName());

        List<UserDb> list = userDbService.list(l1);
        //将UserDb的密码给隐藏为******
        list.forEach(item -> {
            item.setDbPassword("******");
        });
        return Result.success(list);
    }




    @GetMapping("/selectAllTable")
    @ApiOperation("查找指定数据库的所有表")
    public Result<List<String>> selectById(@ApiParam(name = "id", value = "数据库连接id") Long id, String dbName) {
        UserDb userDb = userDbService.getById(id);
        String url = DBUtil.getUrl(userDb.getIp(), userDb.getPort(), dbName);
        String username = userDb.getDbUserName();
        String password = userDb.getDbPassword();

        List<String> result = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            DatabaseMetaData metaData = connection.getMetaData();

            ResultSet resultSet = metaData.getTables(dbName, null, "%", new String[]{"TABLE"});

            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                System.out.println(tableName);
                result.add(tableName);
            }

            return Result.success(result);
        } catch (SQLException e) {
            e.printStackTrace();
            return Result.error("数据库连接错误");
        }
    }

    @Resource
    private DBController dbController;

    @GetMapping("/selectAllDB")
    @ApiOperation("查找指定连接中的所有数据库")
    public Result<List<String>> selectAllDB(Long id) {
        UserDb userDb = userDbService.getById(id);
        String url = DBUtil.getUrl(userDb.getIp(), userDb.getPort(), "");
        Result<List<String>> listResult = dbController.showDatabases(url, userDb.getDbUserName(), userDb.getDbPassword());
        return listResult;
    }


//    @GetMapping("/selectTableData")
//    @ApiOperation("查找表的数据")
//    public Result<Map<String,List<Map<String, String>>>> selectTableData(Long id, String dbName,String tableName) {
//        LambdaQueryWrapper<UserDb> l1 = new LambdaQueryWrapper<>();
//        l1.eq(UserDb::getId, id);
//        UserDb userDb = userDbService.getOne(l1);
//        String url = DBUtil.getUrl(userDb.getIp(), userDb.getPort(), dbName);
//
//
//
//        try (Connection conn = DriverManager.getConnection(url, userDb.getDbUserName(), userDb.getDbPassword());
//             Statement stmt = conn.createStatement()) {
//            //连接指定的数据库
//            String str="use "+dbName+";";
//            stmt.executeUpdate(str);
//
//
//            String sql = "SELECT * FROM " + tableName;
//            ResultSet rs = stmt.executeQuery(sql);
//
//            ResultSetMetaData metaData = rs.getMetaData();
//            int columnCount = metaData.getColumnCount();
//
//            List<Map<String,String>> result=new ArrayList<>();
//            while (rs.next()) {
//                Map<String, String> data = new HashMap<>();
//                for (int i = 1; i <= columnCount; i++) {
//
//                    String columnName = metaData.getColumnName(i);
//                    String columnValue = rs.getString(i);
//
//                    // 明确处理null值
//                    if (columnValue == null) {
//                        columnValue = "null";  // 或者使用其他占位符
//                    }
//
//                    data.put(columnName, columnValue);
//                }
//                result.add(data);
//            }
//
//            Map<String,List<Map<String, String>> >resultData=new HashMap<>();
//            resultData.put("dataKey",result);
//            return Result.success(resultData);
//        } catch (SQLException e) {
//            e.printStackTrace();
//            return Result.error("数据库连接错误 " + e.getMessage());
//        }
//    }

    @GetMapping("/selectTableData")
    @ApiOperation("查找表的数据")
    public Result<TableDTO> selectTableData(Long id, String dbName, String tableName) {
        LambdaQueryWrapper<UserDb> l1 = new LambdaQueryWrapper<>();
        l1.eq(UserDb::getId, id);
        UserDb userDb = userDbService.getOne(l1);
        String url = DBUtil.getUrl(userDb.getIp(), userDb.getPort(), dbName);

        try (Connection conn = DriverManager.getConnection(url, userDb.getDbUserName(), userDb.getDbPassword());
             Statement stmt = conn.createStatement()) {

            //连接指定的数据库
            String str = "use " + dbName + ";";
            stmt.executeUpdate(str);

            String sql = "SELECT * FROM " + tableName;
            ResultSet rs = stmt.executeQuery(sql);

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 初始化列名列表
            List<String> columnNames = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(metaData.getColumnName(i));
            }

            // 初始化结果列表
            List<Map<String, String>> tableData = new ArrayList<>();
            while (rs.next()) {
                Map<String, String> rowData = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    String columnValue = rs.getString(i);
                    // 明确处理null值
                    if (columnValue == null) {
                        columnValue = "null";  // 或者使用其他占位符
                    }
                    rowData.put(columnName, columnValue);
                }
                tableData.add(rowData);
            }

            // 封装返回的DTO
            TableDTO result = new TableDTO();
            result.setTableData(tableData);
            result.setColName(columnNames);

            return Result.success(result);
        } catch (SQLException e) {
            e.printStackTrace();
            return Result.error("数据库连接错误 " + e.getMessage());
        }
    }
    //用户新增连接
    @PostMapping("/addDB")
    @ApiOperation("新增数据库")
//    public Result<String> addDB(String ip,String port, String userName,String dbUserName, String dbPassword, String connectionName ) {
    public Result<String> addDB(@RequestBody UserDb userDb) {

//        return userDbService.save(userDb);
        userDbService.save(userDb);
        return Result.success("新增成功");
    }
}

